package pkg461project;

import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

/**
 *
 * @author ahall1
 */
public class DatabaseInsert {

    private Connection connection;

    public DatabaseInsert() throws ClassNotFoundException, SQLException {
        // Prepare a statement to insert a record
        String url = "jdbc:oracle:thin:@localhost:1521:xe";
        String user = "ajhallameyer";
        String password = "Aj101989";

        // Load the JDBC driver
        String driverName = "oracle.jdbc.driver.OracleDriver";
        Class.forName(driverName);

        connection = DriverManager.getConnection(url, user, password);
    }

    public int insertBranch(Branch branch) {
        try {
            String query = "INSERT INTO BRANCH("
                    + "manager_num,"
                    + "telephone_number,"
                    + "fax_number,"
                    + "street,"
                    + "city,"
                    + "state,"
                    + "zipcode)"
                    + "VALUES(?, ?, ?, ?, ?, ?, ?)";

            PreparedStatement ps;

            ps = connection.prepareStatement(query);

            ps.setInt(1, branch.getManager_num());
            ps.setString(2, branch.getTelephone_number());
            ps.setString(3, branch.getFax_number());
            ps.setString(4, branch.getState());
            ps.setString(5, branch.getCity());
            ps.setString(6, branch.getState());
            ps.setInt(7, branch.getZipcode());

            ps.executeUpdate();

            query = "SELECT branch_num,"
                    + "manager_num,"
                    + "telephone_number,"
                    + "fax_number,"
                    + "street,"
                    + "city,"
                    + "state,"
                    + "zipcode "
                    + "FROM branch "
                    + "WHERE "
                    + "manager_num = ? AND "
                    + "telephone_number = ? AND "
                    + "fax_number = ? AND "
                    + "street = ? AND "
                    + "city = ? AND "
                    + "state = ? AND "
                    + "zipcode = ?";

            ps = connection.prepareStatement(query);

            ps.setInt(1, branch.getManager_num());
            ps.setString(2, branch.getTelephone_number());
            ps.setString(3, branch.getFax_number());
            ps.setString(4, branch.getState());
            ps.setString(5, branch.getCity());
            ps.setString(6, branch.getState());
            ps.setInt(7, branch.getZipcode());


            ResultSet rs = ps.executeQuery();


            if (rs.next()) {
                return rs.getInt(1);
            }
            return 0;

        } catch (SQLException ex) {
            System.out.println(ex.getMessage());
            return 0;
        }
    }

    public int insertEmployee(Employee employee, int branch_num) {
        try {
            String query = "INSERT INTO Employee("
                    + "name,"
                    + "sex,"
                    + "street,"
                    + "city,"
                    + "state,"
                    + "zipcode,"
                    + "employee_num)"
                    + "VALUES(?, ?, ?, ?, ?, ?,?)";

            PreparedStatement ps;

            ps = connection.prepareStatement(query);

            ps.setString(1, employee.getName());
            ps.setString(2, employee.getSex());
            ps.setString(3, employee.getStreet());
            ps.setString(4, employee.getCity());
            ps.setString(5, employee.getState());
            ps.setInt(6, employee.getZipcode());
            ps.setInt(7, 0);

            ps.executeUpdate();

            query = "SELECT employee_num,"
                    + "name,"
                    + "sex,"
                    + "street,"
                    + "city,"
                    + "state,"
                    + "zipcode "
                    + "FROM employee "
                    + "WHERE "
                    + "name = ? AND "
                    + "sex = ? AND "
                    + "street = ? AND "
                    + "city = ? AND "
                    + "state = ? AND "
                    + "zipcode = ?";

            ps = connection.prepareStatement(query);

            ps.setString(1, employee.getName());
            ps.setString(2, employee.getSex());
            ps.setString(3, employee.getStreet());
            ps.setString(4, employee.getCity());
            ps.setString(5, employee.getState());
            ps.setInt(6, employee.getZipcode());


            ResultSet rs = ps.executeQuery();

            if (rs.next()) {
                employee.setEmployee_num(rs.getInt(1));

                query = "INSERT INTO works_at("
                        + "employee_num,"
                        + "branch_num)"
                        + "VALUES(?, ?)";

                ps = connection.prepareStatement(query);

                ps.setInt(1, employee.getEmployee_num());
                ps.setInt(2, branch_num);

                ps.executeUpdate();

                return rs.getInt(1);
            }
            return 0;
        } catch (SQLException ex) {
            System.out.println(ex.getMessage());
            return 0;
        }
    }

    public int insertClient(Client client) {
        try {
            String query = "INSERT INTO CLIENT("
                    + "name,"
                    + "max_rent,"
                    + "preferred_accomodation,"
                    + "work_number,"
                    + "home_number)"
                    + "VALUES(?, ?, ?, ?, ?)";

            PreparedStatement ps;

            ps = connection.prepareStatement(query);

            ps.setString(1, client.getName());
            ps.setInt(2, client.getMax_rent());
            ps.setString(3, client.getPreferred_accomodation());
            ps.setString(4, client.getWork_number());
            ps.setString(5, client.getHome_number());

            ps.executeUpdate();

            query = "SELECT client_num,"
                    + "name,"
                    + "max_rent,"
                    + "preferred_accomodation,"
                    + "work_number,"
                    + "home_number "
                    + "FROM client "
                    + "WHERE "
                    + "name = ? AND "
                    + "max_rent = ? AND "
                    + "preferred_accomodation = ? AND "
                    + "work_number = ? AND "
                    + "home_number = ?";

            ps = connection.prepareStatement(query);

            ps.setString(1, client.getName());
            ps.setInt(2, client.getMax_rent());
            ps.setString(3, client.getPreferred_accomodation());
            ps.setString(4, client.getWork_number());
            ps.setString(5, client.getHome_number());

            ResultSet rs = ps.executeQuery();

            if (rs.next()) {
                return rs.getInt(1);
            }
            return 0;
        } catch (SQLException ex) {
            System.out.println(ex.getMessage());
            return 0;
        }
    }

    public int insertBusinessOwner(Business_owner businessOwner) {
        try {
            String tempStr;
            ArrayList<String> tempArr = new ArrayList<String>();
            String query = "INSERT INTO BUSINESS_OWNERS("
                    + "name,"
                    + "business_type,"
                    + "contact_name)"
                    + "VALUES(?, ?, ?)";

            PreparedStatement ps;

            ps = connection.prepareStatement(query);

            ps.setString(1, businessOwner.getName());
            ps.setString(2, businessOwner.getBusiness_type());
            ps.setString(3, businessOwner.getContact_name());

            ps.executeUpdate();

            query = "SELECT owner_num,"
                    + "name,"
                    + "business_type,"
                    + "contact_name "
                    + "FROM business_owners "
                    + "WHERE "
                    + "name = ? AND "
                    + "business_owners = ? AND "
                    + "contact_name = ?";

            ps = connection.prepareStatement(query);

            ps.setString(1, businessOwner.getName());
            ps.setString(2, businessOwner.getBusiness_type());
            ps.setString(3, businessOwner.getContact_name());

            ResultSet rs = ps.executeQuery();

            if (rs.next()) {
                businessOwner.setOwner_num(rs.getInt(1));
            }

            tempArr = businessOwner.getTelephone_numbers();
            for (int i = 0; i < tempArr.size(); i++) {
                tempStr = tempArr.get(i);

                query = "INSERT INTO OWNER_TELEPHONE_NUMBERS("
                        + "owner_num,"
                        + "telephone_number)"
                        + "VALUES(?, ?)";

                ps.setInt(1, businessOwner.getOwner_num());
                ps.setString(2, tempStr);

                ps.executeUpdate();
            }

            tempArr = businessOwner.getFax_numbers();
            for (int i = 0; i < tempArr.size(); i++) {
                tempStr = tempArr.get(i);

                query = "INSERT INTO OWNER_FAX_NUMBERS("
                        + "owner_num,"
                        + "fax_number)"
                        + "VALUES(?, ?)";

                ps.setInt(1, businessOwner.getOwner_num());
                ps.setString(2, tempStr);

                ps.executeUpdate();
            }

            return businessOwner.getOwner_num();
        } catch (SQLException ex) {
            System.out.println(ex.getMessage());
            return 0;
        }
    }

    public int insertPrivateOwner(Private_owner privateOwner) {
        try {
            String tempStr;
            ArrayList<String> tempArr = new ArrayList<String>();
            String query = "INSERT INTO private_OWNERS("
                    + "name)"
                    + "VALUES(?)";

            PreparedStatement ps;

            ps = connection.prepareStatement(query);

            ps.setString(1, privateOwner.getName());

            ps.executeUpdate();

            query = "SELECT owner_num,"
                    + "name "
                    + "FROM private_owners "
                    + "WHERE "
                    + "name = ?";

            ps = connection.prepareStatement(query);

            ps.setString(1, privateOwner.getName());

            ResultSet rs = ps.executeQuery();

            if (rs.next()) {
                privateOwner.setOwner_num(rs.getInt(1));
            }

            tempArr = privateOwner.getTelephone_numbers();
            for (int i = 0; i < tempArr.size(); i++) {
                tempStr = tempArr.get(i);

                query = "INSERT INTO OWNER_TELEPHONE_NUMBERS("
                        + "owner_num,"
                        + "telephone_number)"
                        + "VALUES(?, ?)";

                ps.setInt(1, privateOwner.getOwner_num());
                ps.setString(2, tempStr);

                ps.executeUpdate();
            }

            tempArr = privateOwner.getFax_numbers();
            for (int i = 0; i < tempArr.size(); i++) {
                tempStr = tempArr.get(i);

                query = "INSERT INTO OWNER_FAX_NUMBERS("
                        + "owner_num,"
                        + "fax_number)"
                        + "VALUES(?, ?)";

                ps.setInt(1, privateOwner.getOwner_num());
                ps.setString(2, tempStr);

                ps.executeUpdate();
            }

            return privateOwner.getOwner_num();
        } catch (SQLException ex) {
            System.out.println(ex.getMessage());
            return 0;
        }
    }

    public int insertProperty(Property property) {
        try {
            String query = "INSERT INTO PROPERTY("
                    + "property_type,"
                    + "num_bedrooms,"
                    + "square_footage,"
                    + "last_update,"
                    + "monthly_rent,"
                    + "property_owner,"
                    + "min_price,"
                    + "max_price,"
                    + "monthly_management_fee,"
                    + "street,"
                    + "city,"
                    + "state,"
                    + "zipcode)"
                    + "VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

            PreparedStatement ps;

            ps = connection.prepareStatement(query);

            ps.setString(1, property.getProperty_type());
            ps.setInt(2, property.getNum_bedrooms());
            ps.setInt(3, property.getSquare_footage());
            ps.setDate(4, property.getLast_update());
            ps.setInt(5, property.getMonthly_rent());
            ps.setString(6, property.getProperty_owner());
            ps.setInt(7, property.getMin_price());
            ps.setInt(8, property.getMax_price());
            ps.setInt(9, property.getMonthly_management_fee());
            ps.setString(10, property.getStreet());
            ps.setString(11, property.getCity());
            ps.setString(12, property.getState());
            ps.setInt(13, property.getZipcode());

            ps.executeUpdate();

            query = "SELECT property_num,"
                    + "property_type,"
                    + "num_bedrooms,"
                    + "square_footage,"
                    + "last_update,"
                    + "monthly_rent,"
                    + "property_owner,"
                    + "min_price,"
                    + "max_price,"
                    + "monthly_management_fee,"
                    + "street,"
                    + "city,"
                    + "state,"
                    + "zipcode "
                    + "FROM property "
                    + "WHERE "
                    + "property_type = ? AND "
                    + "num_bedrooms = ? AND "
                    + "square_footage = ? AND "
                    + "last_update = ? AND "
                    + "monthly_rent = ? AND "
                    + "property_owner = ? AND "
                    + "min_price = ? AND "
                    + "max_price = ? AND "
                    + "monthly_management_fee = ? AND "
                    + "street = ? AND "
                    + "city = ? AND "
                    + "state = ? AND"
                    + "zipcode = ?";

            ps = connection.prepareStatement(query);

            ps.setString(1, property.getProperty_type());
            ps.setInt(2, property.getNum_bedrooms());
            ps.setInt(3, property.getSquare_footage());
            ps.setDate(4, property.getLast_update());
            ps.setInt(5, property.getMonthly_rent());
            ps.setString(6, property.getProperty_owner());
            ps.setInt(7, property.getMin_price());
            ps.setInt(8, property.getMax_price());
            ps.setInt(9, property.getMonthly_management_fee());
            ps.setString(10, property.getStreet());
            ps.setString(11, property.getCity());
            ps.setString(12, property.getState());
            ps.setInt(13, property.getZipcode());


            ResultSet rs = ps.executeQuery();

            if (rs.next()) {
                return rs.getInt(1);
            }
            return 0;
        } catch (SQLException ex) {
            System.out.println(ex.getMessage());
            return 0;
        }
    }

    public int insertLease(Lease lease, int employee_num, int client_num) {
        try {
            String query = "INSERT INTO LEASE("
                    + "start_date,"
                    + "finish_date,"
                    + "payment_method,"
                    + "duration,"
                    + "deposit_amount,"
                    + "property_num)"
                    + "VALUES(?, ?, ?, ?, ?, ?)";

            PreparedStatement ps;

            ps = connection.prepareStatement(query);

            ps.setDate(1, lease.getStart_date());
            ps.setDate(2, lease.getFinish_date());
            ps.setString(3, lease.getPayment_method());
            ps.setInt(4, lease.getDuration());
            ps.setInt(5, lease.getDeposit_amount());
            ps.setInt(6, lease.getProperty_num());

            ps.executeUpdate();

            query = "SELECT lease_num,"
                    + "start_date,"
                    + "finish_date,"
                    + "payment_method,"
                    + "duration,"
                    + "deposit_amount,"
                    + "property_num "
                    + "FROM lease "
                    + "WHERE "
                    + "start_date = ? AND "
                    + "finish_date = ? AND "
                    + "payment_method = ? AND "
                    + "duration = ? AND "
                    + "deposit_amount = ? AND "
                    + "property_num = ?";

            ps.setDate(1, lease.getStart_date());
            ps.setDate(2, lease.getFinish_date());
            ps.setString(3, lease.getPayment_method());
            ps.setInt(4, lease.getDuration());
            ps.setInt(5, lease.getDeposit_amount());
            ps.setInt(6, lease.getProperty_num());

            ps.executeQuery();

            query = "INSERT INTO COMPLETED("
                    + "employee_num,"
                    + "lease_num)"
                    + "VALUES(?, ?)";

            ps = connection.prepareStatement(query);

            ps.setInt(1, employee_num);
            ps.setInt(2, lease.getLease_num());

            ps.executeUpdate();

            query = "INSERT INTO COMPLETED("
                    + "employee_num,"
                    + "lease_num)"
                    + "VALUES(?, ?)";

            ps = connection.prepareStatement(query);

            ps.setInt(1, employee_num);
            ps.setInt(2, lease.getLease_num());

            ps.executeUpdate();

            query = "INSERT INTO SIGNED("
                    + "client_num,"
                    + "lease_num)"
                    + "VALUES(?, ?)";

            ps = connection.prepareStatement(query);

            ps.setInt(1, client_num);
            ps.setInt(2, lease.getLease_num());

            ps.executeUpdate();

            return lease.getLease_num();

        } catch (SQLException ex) {
            System.out.println(ex.getMessage());
            return 0;
        }
    }

    public int insertNewspaperAd(Newspaper_ad newsAd) {
        try {
            String query = "INSERT INTO NEWSPAPER_AD("
                    + "property_num,"
                    + "newspaper_name,"
                    + "cost_of_ad,"
                    + "date_of_ad,"
                    + "street,"
                    + "city,"
                    + "state,"
                    + "zipcode)"
                    + "VALUES(?, ?, ?, ?, ?, ?, ?, ?)";

            PreparedStatement ps;

            ps = connection.prepareStatement(query);

            ps.setInt(1, newsAd.getProperty_num());
            ps.setString(2, newsAd.getNewspaper_name());
            ps.setInt(3, newsAd.getCost_of_ad());
            ps.setDate(4, newsAd.getDate_of_ad());
            ps.setString(5, newsAd.getStreet());
            ps.setString(6, newsAd.getCity());
            ps.setString(7, newsAd.getState());
            ps.setInt(8, newsAd.getZipcode());

            ps.executeUpdate();

            query = "SELECT newspaper_ad_num, "
                    + "property_num, "
                    + "newspaper_name, "
                    + "cost_of_ad, "
                    + "date_of_ad, "
                    + "street, "
                    + "city, "
                    + "state, "
                    + "zipcode "
                    + "FROM  "
                    + "WHERE "
                    + "property_num = ? AND "
                    + "newspaper_name = ? AND "
                    + "cost_of_ad = ? AND "
                    + "date_of_ad = ? AND "
                    + "street = ? AND "
                    + "city = ? AND "
                    + "state = ? AND "
                    + "zipcode = ?";
            
            ps = connection.prepareStatement(query);
            
            ps.setInt(1, newsAd.getProperty_num());
            ps.setString(2, newsAd.getNewspaper_name());
            ps.setInt(3, newsAd.getCost_of_ad());
            ps.setDate(4, newsAd.getDate_of_ad());
            ps.setString(5, newsAd.getStreet());
            ps.setString(6, newsAd.getCity());
            ps.setString(7, newsAd.getState());
            ps.setInt(8, newsAd.getZipcode());

            ResultSet rs = ps.executeQuery();

            if (rs.next()) {
                return rs.getInt(1);
            }
            return 0;

        } catch (SQLException ex) {
            System.out.println(ex.getMessage());
            return 0;
        }
    }

    public void insertView(int property_num, int client_num, Date viewDate, String comments) {
        try {
            String query = "INSERT INTO VIEWS("
                    + "property_num,"
                    + "client_num,"
                    + "viewDate,"
                    + "comments)"
                    + "VALUES(?, ?, ?, ?)";

            PreparedStatement ps;

            ps = connection.prepareStatement(query);

            ps.setInt(1, property_num);
            ps.setInt(2, client_num);
            ps.setDate(3, viewDate);
            ps.setString(4, comments);
            
            ps.executeUpdate();
            
        } catch (SQLException ex) {
            System.out.println(ex.getMessage());
            return;
        }

    }

    public void close() throws SQLException {
        connection.close();
    }
}
